package com.slipper.common.excel;

import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelUtil {
    public static void parserAndExport(HttpServletResponse response,
            Map<String, String> params, InputStream fs, String fileName) throws Exception {
        response.reset();
        if (fileName == null || fileName.trim().isEmpty()) {
            fileName = "default.xlsx"; // 定义保存的文件名字
        }
        response.setContentType("application/octet-stream");
        String trueName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        response.setHeader("Content-Disposition", "attachment; filename=\"" + trueName + "\"");

        OutputStream os = response.getOutputStream();
        try {
            Workbook workbook = new HSSFWorkbook(fs);
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                if (row != null) {
                    int num = row.getLastCellNum();
                    for (int i = 0; i < num; i++) {
                        HSSFCell cell = row.getCell(i);
                        if (cell != null && CellType.STRING == cell.getCellType()) {
                            String value = cell.getStringCellValue();
                            if (value != null && value.contains("}")) {
                                for (Map.Entry<String, String> entry : params.entrySet()) {
                                    value = value.replace("{" + entry.getKey() + "}", entry.getValue());
                                }
                                cell.setCellValue(value);
                            }
                        }
                    }
                }
            }
            workbook.write(os);
            workbook.close();
        } catch (Exception e) {
            fs.close();
        }
    }
    
    public static void addDataValidation(Workbook workbook, Sheet sheet, String[] dataArr,
            int firstRow, int endRow, int firstCol, int endCol) {
        if (dataArr == null || dataArr.length == 0) {
            return;
        }
        int sheetIndex = workbook.getNumberOfSheets();
        String sheetName = "dictionary" + (sheetIndex + 1);
        Sheet hidden = workbook.createSheet(sheetName);
        Cell cell;
        for (int i = 0, length = dataArr.length; i < length; i++) {
            String name = dataArr[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName(sheetName);
        namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + dataArr.length);
        // 加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

        // 将第二个sheet设置为隐藏
        workbook.setSheetHidden(sheetIndex, true);
        sheet.addValidationData(validation);
    }
}
